前端使用xlsx、file |
您所在的位置：网站首页 › Labview 报表怎么更改列宽 › 前端使用xlsx、file |
前端使用xlsx、file
|
前端使用xlsx、file-saver实现excel导出
前端使用xlsx、file-saver实现excel导出1.下载依赖2.在需要导出excel的页面使用依赖3.实现导出excel文件功能3.1给需要导出的表格赋予id3.2按钮调用导出文件函数3.3函数实现导出功能调整行高调整背景色
4.xlxs-style属性单元格属性单元格样式
前端使用xlsx、file-saver实现excel导出
xlsx-style可以自定义表格样式 1.下载依赖使用命令行下载所需要的依赖，会在在package.json文件内添加。 "dependencies": { "axios": "^0.19.2", "babel-polyfill": "^6.26.0", "element-ui": "^2.15.6", "file-saver": "^2.0.5", "pinyin-match": "^1.2.2", "print-js": "^1.6.0", "vue": "^2.5.2", "vue-router": "^3.0.1", "vuex": "^3.1.2", "xlsx": "^0.17.0", "xlsx-style": "^0.8.13" },
按照id定位到需要导出的表格 handleDownload () { this.outputXLSX('文件名', '#todaytable', this); }, outputXLSX (filename, selector, _this) { let ws = XLSX.utils.table_to_sheet(document.getElementById('todaytable')) //创建一个workbook对象 let wb2 = XLSX.utils.book_new() //把worksheet对象添加进workbook对象，第三个参数是excel中sheet的名字 XLSX.utils.book_append_sheet(wb2, ws, filename) this.setExlStyle(wb2['Sheets'][filename]); // 设置列宽 字号等 this.addRangeBorder(wb2['Sheets'][filename]['!merges'],wb2['Sheets'][filename]) // 单元格合并 let wb_out = XLSXSTYLE.write(wb2, { type: 'buffer'}) try { FileSaver.saveAs(new Blob([wb_out], { type: 'application/octet-stream' }), filename+'.xlsx'); //filename.xlsx 为导出的文件名 } catch (e) { console.log(e, wb_out) ; } return wb_out; }, setExlStyle(data) { let borderAll = { //单元格外侧框线 top: { style: 'thin', }, bottom: { style: 'thin' }, left: { style: 'thin' }, right: { style: 'thin' } }; data['!cols'] = []; for (let key in data) { // 按照数值调整文件字体颜色，数值 col = 'ff0000' } else { col = '000000' } data[key].s = { border: borderAll, alignment: { horizontal: 'center', //水平居中对齐 vertical:'center' }, font:{ // 自定义字体颜色 color: {rgb: col}, sz:11 }, bold:true, numFmt: 0 } // 自定义列宽，每列都一样 // data['!cols'].push({wpx: 80}) } } // 自定义列宽, 每列不一样 data['!cols'] = [{wpx: 40},{wpx: 100},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85}, {wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85} ,{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85},{wpx: 85}]; return data; }, addRangeBorder (range, ws) { let cols = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"]; range.forEach(item => { let style = { s: { border: { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } } } } // 处理合并行 for (let i = item.s.c; i cols[i]}${Number(item.e.r) + 1}`] = ws[`${cols[i]}${Number(item.e.r) + 1}`] || style // 处理合并列 for (let k = item.s.r + 2; k let ws = XLSX.utils.table_to_sheet(document.getElementById('todaytable')) //创建一个workbook对象 let wb2 = XLSX.utils.book_new() //把worksheet对象添加进workbook对象，第三个参数是excel中sheet的名字 XLSX.utils.book_append_sheet(wb2, ws, filename) // 输出查看每列数值 console.log(wb2['Sheets'][filename]) // 表头重新赋值 wb2['Sheets'][filename].D3 = wb2['Sheets'][filename].F3 wb2['Sheets'][filename].E3 = wb2['Sheets'][filename].G3 wb2['Sheets'][filename].F3 = wb2['Sheets'][filename].H3 wb2['Sheets'][filename].G3 = wb2['Sheets'][filename].I3 wb2['Sheets'][filename].H3 = wb2['Sheets'][filename].J3 wb2['Sheets'][filename].I3 = wb2['Sheets'][filename].K3 wb2['Sheets'][filename].J3 = wb2['Sheets'][filename].L3 // 合并重新调整 wb2['Sheets'][filename]['!merges'][1].e.c = 1 wb2['Sheets'][filename]['!merges'][1].s.c = 0 this.setExlStyle(wb2['Sheets'][filename]); // 设置列宽 字号等 this.addRangeBorder(wb2['Sheets'][filename]['!merges'],wb2['Sheets'][filename]) let wb_out = XLSXSTYLE.write(wb2, { type: 'buffer'}) try { FileSaver.saveAs(new Blob([wb_out], { type: 'application/octet-stream' }), filename+'.xlsx'); //trade-publish.xlsx 为导出的文件名 } catch (e) { console.log(e, wb_out) ; } return wb_out; }, 调整行高百度搜了好久，参考了各个大神的做法，最后参考这个文章的实现了 链接: https://blog.csdn.net/jililin123/article/details/126249198#comments_24348365 需要修改xlxs-style的内容 # 第一步 修改nod_modules 里面xlsx-style文件夹下面dist文件夹下的cpexcel.js文件 807: var cpt = cptable; # 第二步 修改xlsx-style文件夹下面ods.js文件 10: return require('./' + 'xlsx').utils; 12: try { return require('./' + 'xlsx').utils; } # 第三步 修改xlsx-style文件夹下面的xlsx.js文件 替换write_ws_xml_data以下方法 var DEF_PPI = 96, PPI = DEF_PPI; function px2pt(px) { return px * 96 / PPI; } function pt2px(pt) { return pt * PPI / 96; } function write_ws_xml_data(ws, opts, idx, wb) { var o = [], r = [], range = safe_decode_range(ws['!ref']), cell, ref, rr = "", cols = [], R, C,rows = ws['!rows']; for(C = range.s.c; C ref = cols[C] + rr; if(ws[ref] === undefined) continue; if((cell = write_ws_xml_cell(ws[ref], ref, ws, opts, idx, wb)) != null) r.push(cell); } if(r.length > 0){ params = ({r:rr}); if(rows && rows[R]) { row = rows[R]; if(row.hidden) params.hidden = 1; height = -1; if (row.hpx) height = px2pt(row.hpx); else if (row.hpt) height = row.hpt; if (height > -1) { params.ht = height; params.customHeight = 1; } if (row.level) { params.outlineLevel = row.level; } } o[o.length] = (writextag('row', r.join(""), params)); } } if(rows) for(; R params = ({r:R+1}); row = rows[R]; if(row.hidden) params.hidden = 1; height = -1; if (row.hpx) height = px2pt(row.hpx); else if (row.hpt) height = row.hpt; if (height > -1) { params.ht = height; params.customHeight = 1; } if (row.level) { params.outlineLevel = row.level; } o[o.length] = (writextag('row', "", params)); } } return o.join(""); }在setExlStyle函数里增加设置行高的属性data[“!rows”] // 行高 data["!rows"] = [{hpx: 40},{hpx: 40},{hpx: 40}]; // 列宽 data['!cols'] = [{wpx: 20},{wpx: 80},{wpx: 35},{wpx: 55},{wpx: 55},{wpx: 55},{wpx: 55}, {wpx: 55},{wpx: 55},{wpx: 55},{wpx: 55},{wpx: 55},{wpx: 55},{wpx: 55},{wpx: 55},{wpx: 55}]; return data; 调整背景色用到属性fill的bgColor。 COLOR_SPEC 指设定颜色的对象，取值如下： { rgb: “FFFFAA00” } 十六进制ARGB值 { theme: “1”} 主题颜色的整数索引，默认是0。 更多内容参考： https://www.imooc.com/article/305180 4.xlxs-style属性 单元格属性
|
【本文地址】
公司简介
联系我们
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |